﻿using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using TimeSplicerModule.DomainModels;
using TimeSplicerModule.Factories;
using Excel = Microsoft.Office.Interop.Excel;

namespace TimeSplicerModule.Services
{
    public interface IReportsService
    {
        void FullProjectReport(Guid projectGuid);
    }

    public class ReportsService : IReportsService
    {
        private readonly IProjectService _projectService;
        private readonly ICategoryFactory _categoryFactory;

        public ReportsService(IProjectService projectService, ICategoryFactory categoryFactory)
        {
            _projectService = projectService;
            _categoryFactory = categoryFactory;
        }

        public void FullProjectReport(Guid projectGuid)
        {

            //Create the overall project page and then the user pages
            var project = _projectService.Get(projectGuid);


            Excel.Application excel = new Excel.Application();
            Excel.Workbook book = excel.Workbooks.Add();
            Excel.Worksheet summary = book.Worksheets[1];

            projectSummary(summary, project);

            project.ProjectUsers = project.ProjectUsers.OrderBy(o => o.ProjectRole.Hierarchy).ToList();

            excel.ActiveWindow.DisplayGridlines = false;
            Excel.Worksheet latest = summary;

            foreach (var u in project.ProjectUsers)
            {
                Excel.Worksheet s = book.Worksheets.Add(After: latest);

                userSummary(s, project, u);

                excel.ActiveWindow.DisplayGridlines = false;
                latest = s;
            }


            summary.Activate();
            excel.Visible = true;
        }



        private void projectSummary(Excel.Worksheet sheet, ProjectModel project)
        {
            string sheetName = project.Name.Replace("/", " ");

            try
            {
                if (sheetName.Length > 15)
                    sheet.Name = sheetName.Substring(0, 15);
                else
                    sheet.Name = sheetName;
            }
            catch (Exception ex)
            {
                sheet.Name = "err";
            }

            createHeader(sheet, project.Name);


            // format summary areas
            // need category

            int row = 11;
            double totalHours = 0D;

            if (project.Status.Status == Infrastucture.Status.Completed)
                ++row;

            Excel.Range totalsRange = sheet.Range["B5:C" + row];

            totalsRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            totalsRange.Font.Name = "Verdana";
            totalsRange.Font.Size = 11;
            totalsRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            sheet.Range["B5:B" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            sheet.Range["C5:C" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["C5:C" + row].Font.Bold = true;

            sheet.Range["B2"].ColumnWidth = 27;
            sheet.Range["C2"].ColumnWidth = 35;
            sheet.Range["I2"].ColumnWidth = 11;

            // values
            sheet.Range["B5"].Value2 = "Status:";
            sheet.Range["B6"].Value2 = "Category";
            sheet.Range["B7"].Value2 = "Project Hours:";
            sheet.Range["B8"].Value2 = "Number of Tasks:";
            sheet.Range["B9"].Value2 = "Average Time Per Task:";
            sheet.Range["B10"].Value2 = "Project Users";
            sheet.Range["B11"].Value2 = "Started Date";

            if (project.Status.Status == Infrastucture.Status.Completed)
            {
                sheet.Range["B" + row].Value2 = "Completed Date";
                sheet.Range["C" + row].Value2 = project.CompletedDate.ToShortDateString();
            }


            sheet.Range["C5"].Value2 = project.Status.Name;
            sheet.Range["C6"].Value2 = project.Category.Name;
            sheet.Range["C7"].Value2 = String.Format("{0:0.00}", project.ActualHours);
            sheet.Range["C8"].Value2 = project.Tasks.Count;
            sheet.Range["C9"].Value2 = String.Format("{0:0.00}", project.ActualHours / project.Tasks.Count);
            sheet.Range["C10"].Value2 = project.ProjectUsers.Count;
            sheet.Range["C11"].Value2 = project.StartedDate.ToShortDateString();

            // description
            sheet.Range["D5"].ColumnWidth = 3;
            sheet.Range["E5"].ColumnWidth = 14;
            Excel.Range descRange = sheet.get_Range("E5", "I8");
            descRange.Font.Name = "Verdana";
            descRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
            descRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            sheet.Range["E5:E" + row].MergeCells = true;
            sheet.Range["E5"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["E5"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            sheet.Range["E5"].WrapText = true;
            sheet.Range["F5:I" + row].MergeCells = true;
            sheet.Range["F5"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["F5"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            sheet.Range["F5"].WrapText = true;

            sheet.Range["E5"].Value2 = "Project Description:";
            sheet.Range["F5"].Value2 = project.Description;




            row += 3;
            int lastRow = row + project.ProjectUsers.Count;

            // get ranges
            Excel.Range range = sheet.get_Range("B" + row, "I" + lastRow);
            Excel.Range userRange = sheet.get_Range("K" + row, "L" + lastRow);
            userRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            range.Font.Name = "Verdana";
            sheet.Range["D" + row + ":G" + row].MergeCells = true;
            sheet.Range["H" + row + ":I" + row].MergeCells = true;

            // headers
            sheet.Range["B" + row].Value2 = "User";
            sheet.Range["C" + row].Value2 = "Project Role";
            sheet.Range["D" + row].Value2 = "Task Count";
            sheet.Range["H" + row].Value2 = "Task Hours";

            Excel.Range taskHeaderRange = sheet.Range["B" + row + ":I" + row];
            taskHeaderRange.Font.Bold = true;
            taskHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            taskHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));

            ++row;

            project.ProjectUsers = project.ProjectUsers.OrderBy(o => o.ProjectRole.Hierarchy).ToList();

            // user simmary
            foreach (var u in project.ProjectUsers)
            {
                // need t oget user tasks
                var userTasks = project.Tasks.Where(x => x.AssignedToGuid == u.UserGuid).ToList();

                // formatting
                Excel.Range r = sheet.Range["B" + row + ":I" + row];
                sheet.Range["D" + row + ":G" + row].MergeCells = true;
                sheet.Range["H" + row + ":I" + row].MergeCells = true;
                r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                sheet.Range["D" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["H" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["H" + row].NumberFormat = "0.00";


                sheet.Range["B" + row].Value2 = u.User.FullName;
                sheet.Range["C" + row].Value2 = u.ProjectRole.Name;
                sheet.Range["D" + row].Value2 = userTasks.Count;
                sheet.Range["H" + row].Value2 = String.Format("{0:0.00}", userTasks.Sum(x => x.ActualHours));


                // chart area
                sheet.Range["K" + row].Value2 = u.User.FullName;
                sheet.Range["L" + row].Value2 = String.Format("{0:0.00}", userTasks.Sum(x => x.ActualHours));
                    

                ++row;
                totalHours += userTasks.Sum(x => x.ActualHours);
            }


            Excel.ChartObjects chartObjects = sheet.ChartObjects(Type.Missing);

            // pie chart
            Excel.Chart taskChart = chartObjects.Add(700, 75, 500, 300).Chart;

            // format chart
            taskChart.SetSourceData(userRange, Type.Missing);
            taskChart.ChartType = Excel.XlChartType.xlDoughnut;
            taskChart.HasTitle = true;
            taskChart.ChartTitle.Text = "User Hours";
            taskChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue);



           // category summary
            row += 2;

            var allCats = _categoryFactory.GetList();
            List<CategoryModel> categories = new List<CategoryModel>();
            List<CategoryModel> subCategories = new List<CategoryModel>();

            // get unique categories
            // this isnt working correctly
            // Need to check ad see if user has sub tassk assigned
            foreach (var t in project.Tasks)
            {
                if (t.TaskGuid != Guid.Empty)
                {
                    var cat = categories.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid);

                    if (cat == null)
                    {
                        cat = allCats.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid);

                        if (cat != null)
                            categories.Add(cat);
                    }

                    if (cat != null)
                    {
                        var sub = subCategories.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid);

                        if (sub == null)
                        {
                            sub = allCats.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid);
                            var assigned = project.Tasks.FirstOrDefault(x => x.SubCategoryGuid == sub.CategoryGuid);

                            if (sub != null && assigned != null && sub.Name != "Unassigned")
                                subCategories.Add(sub);
                        }
                    }
                }
            }


            // headers
            // formatting
            sheet.Range["D" + row + ":G" + row].MergeCells = true;
            sheet.Range["H" + row + ":I" + row].MergeCells = true;

            sheet.Range["B" + row].Value2 = "Task Category";
            sheet.Range["C" + row].Value2 = "Task Sub Category";
            sheet.Range["D" + row].Value2 = "Hours";
            sheet.Range["D" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["H" + row].Value2 = "%";
            sheet.Range["H" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


            // hcart headers
            sheet.Range["K" + row].Value2 = "Category";
            sheet.Range["L" + row].Value2 = "Hours";


            Excel.Range catHeaderRange = sheet.Range["B" + row + ":I" + row];
            catHeaderRange.Font.Name = "Verdana";
            catHeaderRange.Font.Bold = true;
            catHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            catHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));

            
            ++row;
            int startRow = row;

            foreach (var c in categories)
            {
                var subs = subCategories.Where(x => x.ParentCategoryGuid == c.CategoryGuid).ToList();

                foreach (var s in subCategories)
                {
                    var tasks = project.Tasks.Where(x => x.CategoryGuid == c.CategoryGuid
                        && x.SubCategoryGuid == s.CategoryGuid).ToList();


                    // formatting
                    Excel.Range r = sheet.Range["B" + row + ":I" + row];
                    r.Font.Name = "Verdana";
                    sheet.Range["D" + row + ":G" + row].MergeCells = true;
                    sheet.Range["H" + row + ":I" + row].MergeCells = true;
                    r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                    r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                    sheet.Range["D" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    sheet.Range["H" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    sheet.Range["D" + row].NumberFormat = "0.00";
                    sheet.Range["H" + row].NumberFormat = "00%";



                    sheet.Range["B" + row].Value2 = c.Name;
                    sheet.Range["C" + row].Value2 = s.Name;
                    sheet.Range["D" + row].Value2 = tasks.Sum(x => x.ActualHours);
                    sheet.Range["H" + row].Value2 = tasks.Sum(x => x.ActualHours) / totalHours;



                    // todo: invisible 
                    sheet.Range["K" + row].Value2 = c.Name + "/" + s.Name;
                    sheet.Range["L" + row].Value2 = tasks.Sum(x => x.ActualHours);


                    ++row;
                }
            }


            Excel.Range catRange = sheet.get_Range("K" + row, "L" + (row - 1));
            catRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));



            // todo: chart
            Excel.Chart catChart = chartObjects.Add(700, 400, 500, 300).Chart;

            // format chart
            catChart.SetSourceData(catRange, Type.Missing);
            catChart.ChartType = Excel.XlChartType.xlDoughnut;
            catChart.HasTitle = true;
            catChart.ChartTitle.Text = "Task Category Hours";
            catChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent);
        }

        private void userSummary(Excel.Worksheet sheet, ProjectModel project, ProjectUserModel user)
        {
            string sheetName = user.User.FirstName.Replace("/", " ");

            try
            {
                if (sheetName.Length > 15)
                    sheet.Name = sheetName.Substring(0, 15);
                else
                    sheet.Name = sheetName;
            }
            catch (Exception ex)
            {
                sheet.Name = "err";
            }

            createHeader(sheet, user.User.FullName);

            var userTasks = project.Tasks.Where(x => x.AssignedToGuid == user.User.UserGuid).OrderByDescending(o => o.Hours).ToList();
            int row = 8;
            double totalHours = userTasks.Sum(x => x.ActualHours);

  

            Excel.Range totalsRange = sheet.Range["B5:C" + row];

            totalsRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0, 176, 240));
            totalsRange.Font.Name = "Verdana";
            totalsRange.Font.Size = 11;
            totalsRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            sheet.Range["B5:B" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            sheet.Range["C5:C" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["C5:C" + row].Font.Bold = true;

            sheet.Range["B2"].ColumnWidth = 27;
            sheet.Range["C2"].ColumnWidth = 30;
            sheet.Range["D2"].ColumnWidth = 15;
            sheet.Range["E2"].ColumnWidth = 15;
            sheet.Range["F2"].ColumnWidth = 15;
            sheet.Range["G2"].ColumnWidth = 15;
            sheet.Range["H2"].ColumnWidth = 15;
            sheet.Range["I2"].ColumnWidth = 15;
            sheet.Range["J2"].ColumnWidth = 40;

            // values
            sheet.Range["B5"].Value2 = "Project Role:";
            sheet.Range["B6"].Value2 = "Project Hours:";
            sheet.Range["B7"].Value2 = "Number of Tasks:";
            sheet.Range["B8"].Value2 = "Average Time Per Task:";


            sheet.Range["C5"].Value2 = user.ProjectRole.Name;
            sheet.Range["C6"].Value2 = totalHours; //userTasks.Sum(x => x.Hours);
            sheet.Range["C7"].Value2 = userTasks.Count;
            sheet.Range["C8"].Value2 = String.Format("{0:0.00}", totalHours / userTasks.Count);
            

            // now onto tasks
            // headers

            var allCats = _categoryFactory.GetList();
            row += 3;
            int lastRow = row + userTasks.Count;



            // get ranges
            Excel.Range range = sheet.get_Range("B" + row, "J" + lastRow);
            Excel.Range userRange = sheet.get_Range("K" + row, "L" + lastRow);
            userRange.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));

            range.Font.Name = "Verdana";
            sheet.Range["D" + row + ":F" + row].MergeCells = true;
            //sheet.Range["H" + row + ":I" + row].MergeCells = true;

            // headers
            sheet.Range["B" + row].Value2 = "Task";
            sheet.Range["C" + row].Value2 = "Status";
            sheet.Range["D" + row].Value2 = "Category";
            sheet.Range["G" + row].Value2 = "Started";
            sheet.Range["H" + row].Value2 = "Completed";
            sheet.Range["I" + row].Value2 = "Hours";
            sheet.Range["J" + row].Value2 = "Description";

            Excel.Range taskHeaderRange = sheet.Range["B" + row + ":J" + row];
            taskHeaderRange.Font.Bold = true;
            taskHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            taskHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));

            ++row;

            // need to get categories
            foreach (var t in userTasks)
            {


                var cat = allCats.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid);
                var sub = allCats.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid);

                sheet.Range["B" + row].Value2 = t.Name;
                sheet.Range["C" + row].Value2 = t.Status.Name;
                sheet.Range["D" + row].Value2 = cat.Name + "/" + sub.Name;
                sheet.Range["G" + row].Value2 = t.StartedDate.ToShortDateString();

                if (t.Status.Status == Infrastucture.Status.Completed)
                    sheet.Range["H" + row].Value2 = t.CompletedDate.ToShortDateString();

                sheet.Range["I" + row].Value2 = t.ActualHours;
                sheet.Range["I" + row].NumberFormat = "0.00";
                sheet.Range["I" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["J" + row].Value2 = t.Description;


                // format
                Excel.Range r = sheet.Range["B" + row + ":J" + row];
                r.Font.Name = "Verdana";
                r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                sheet.Range["E" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                sheet.Range["F" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                


                ++row;
            }

            // the categories
            row += 2;

            List<CategoryModel> categories = new List<CategoryModel>();
            List<CategoryModel> subCategories = new List<CategoryModel>();
            // get unique categories
            foreach (var t in userTasks)
            {
                if (t.TaskGuid != Guid.Empty)
                {
                    var cat = categories.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid);

                    if (cat == null)
                    {
                        cat = allCats.FirstOrDefault(x => x.CategoryGuid == t.CategoryGuid);

                        if (cat != null)
                            categories.Add(cat);
                    }

                    if (cat != null)
                    {
                        var sub = subCategories.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid);

                        if (sub == null)
                        {
                            sub = allCats.FirstOrDefault(x => x.CategoryGuid == t.SubCategoryGuid);
                            var assigned = userTasks.FirstOrDefault(x => x.SubCategoryGuid == sub.CategoryGuid);

                            if (sub != null && assigned != null && sub.Name != "Unassigned")
                                subCategories.Add(sub);
                        }
                    }
                }
            }


            // headers
            // formatting
            //sheet.Range["D" + row + ":G" + row].MergeCells = true;
            //sheet.Range["H" + row + ":I" + row].MergeCells = true;

            sheet.Range["B" + row].Value2 = "Task Category";
            sheet.Range["C" + row].Value2 = "Task Sub Category";
            sheet.Range["D" + row].Value2 = "Hours";
            sheet.Range["D" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            sheet.Range["E" + row].Value2 = "%";
            sheet.Range["E" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


            // hcart headers
            //sheet.Range["K" + row].Value2 = "Category";
            //sheet.Range["L" + row].Value2 = "Hours";


            Excel.Range catHeaderRange = sheet.Range["B" + row + ":E" + row];
            catHeaderRange.Font.Name = "Verdana";
            catHeaderRange.Font.Bold = true;
            catHeaderRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            catHeaderRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(142, 169, 219));


            ++row;
            int startRow = row;

            foreach (var c in categories)
            {
                var subs = subCategories.Where(x => x.ParentCategoryGuid == c.CategoryGuid).ToList();

                foreach (var s in subCategories)
                {
                    var tasks = userTasks.Where(x => x.CategoryGuid == c.CategoryGuid
                        && x.SubCategoryGuid == s.CategoryGuid).ToList();


                    // formatting
                    Excel.Range r = sheet.Range["B" + row + ":E" + row];
                    r.Font.Name = "Verdana";
                   // sheet.Range["D" + row + ":G" + row].MergeCells = true;



                   // sheet.Range["H" + row + ":I" + row].MergeCells = true;
                    r.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));
                    r.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(68, 114, 196));
                    sheet.Range["D" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    sheet.Range["E" + row].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    sheet.Range["D" + row].NumberFormat = "0.00";
                    sheet.Range["E" + row].NumberFormat = "00%";



                    sheet.Range["B" + row].Value2 = c.Name;
                    sheet.Range["C" + row].Value2 = s.Name;
                    sheet.Range["D" + row].Value2 = tasks.Sum(x => x.ActualHours);
                    sheet.Range["E" + row].Value2 = tasks.Sum(x => x.ActualHours) / totalHours;



                    // todo: invisible 
                    //sheet.Range["K" + row].Value2 = c.Name + "/" + s.Name;
                    //sheet.Range["L" + row].Value2 = tasks.Sum(x => x.Hours);


                    ++row;
                }
            }

        }



        // Helpers
        private void createHeader(Excel.Worksheet sheet, string headerText)
        {
            sheet.Range["A1:X3"].MergeCells = true;

            Excel.Range header = sheet.Range["A1"];

            header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            header.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            header.Font.Name = "Verdana";
            header.Font.Bold = true;
            header.Font.Size = 20;
            header.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(64, 73, 83));
            header.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 255));


            header.Value2 = headerText;
        }
    }
}
